/**
 *
 */
package stephen.data.dict;

import java.nio.file.StandardCopyOption;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;

import com.alibaba.fastjson.JSON;

import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

/**
 * @author Stephen An
 * @date 2019年11月29日 下午10:46:01
 */
public class ExcelTools {
	public static void main(String[] args) {
		String sampleBackupExcel = "D:\\我的文档\\桌面\\DataDict\\Sample\\Sample.bak.xlsx";
		String sampleExcel = "D:\\我的文档\\桌面\\DataDict\\Sample\\Sample.xlsx";
		String resultsExcel = "D:\\我的文档\\桌面\\DataDict\\Results.xlsx";

		FileUtil.copyFile(sampleBackupExcel, sampleExcel, StandardCopyOption.REPLACE_EXISTING);

		ExcelWriter writer = ExcelUtil.getWriter(sampleExcel);
		Workbook wb = writer.getWorkbook();
		writer.getStyleSet().setAlign(HorizontalAlignment.LEFT, VerticalAlignment.BOTTOM);

		Map<String, List<List<String>>> tablesMap = null;
		tablesMap = JSON.parseObject(FileUtil.readString(TableTools.jsonData, "utf-8"), Map.class);

		int i = 0;
		Iterator<Map.Entry<String, List<List<String>>>> mapIterator = tablesMap.entrySet().iterator();
		while (mapIterator.hasNext()) {
			Entry<String, List<List<String>>> entry = mapIterator.next();
			String sheetName = entry.getKey();

			Sheet sampleSheet = wb.cloneSheet(0);
			writer.getWorkbook().setSheetName(wb.getSheetIndex(sampleSheet), sheetName);
			writer.setSheet(sheetName);
			writer.passRows(2);

			List<List<String>> rowList = entry.getValue();

			writer.write(rowList);
			System.out.println("Complete Writing : " + sheetName + " [" + rowList.size() + "]");

			writer.setSheet("TableList").getOrCreateCell(0, 2 + i).setCellFormula("HYPERLINK(\"#'" + sheetName + "'!A3\",\"" + sheetName + "\")");
			writer.setSheet("TableList").getOrCreateCell(1, 2 + i++).setCellValue(rowList.size());

			mapIterator.remove();
		}

		writer.setDestFile(FileUtil.newFile(resultsExcel));
		writer.flush();
		System.out.println("Complete Saving Excel");

		writer.close();

	}
}
